﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using cmsservice.Entity;
using System.Data.SqlClient;

namespace cmsservice.DAL
{
    public class VM_DMS_GetNewsByCodeDAL
    {
        /// <summary>
        /// 财经资讯
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<VM_DMS_NewsList> GetCaiJingZiXun(int top)
        {
            string sql = @"select top " + top + @" Id=newsid_num,Title=F006V_12001,Step=0,Url='',PTime=f035d_12001,Source='',ImageUrl='',Nature=0,Summary=F015V_12001 FROM (
	SELECT distinct * FROM (
		SELECT  top " + top + @" newsid_num,f006v_12001,f007v_12001,f013v_12001,f015v_12001,f035d_12001,f047v_12001,f045v_12001,rectime FROM vm_dms_getnewsbycode with(nolock)
		WHERE (code in ('3010202','3050502','3050602','6010102','6050102','6050203','6060103','6060202','6070404','6070502','6080102','6080104','3070102')
		AND f004v_12001=5 AND f035d_12001>=CAST(GETDATE() AS DATE))
		ORDER BY f035d_12001 DESC
	) AS t1
	UNION ALL
	SELECT * FROM (
		SELECT  top " + top + @" newsid_num,f006v_12001,f007v_12001,f013v_12001,f015v_12001,f035d_12001,f047v_12001,f045v_12001,rectime FROM vm_dms_getnewsbycode with(nolock)
		WHERE code = '3070102' AND f004v_12001=3 AND CAST(SUBSTRING(CONVERT( CHAR( 20 ),f035d_12001 , 120 ),12,2) AS INT)>=15 ORDER BY f035d_12001 DESC
	) AS t2
) AS T ORDER BY f035d_12001 DESC";
            return SqlHelper.GetDataByColumnsLst<VM_DMS_NewsList>(DBConn.NewDMSConnstr, sql, null);
        }

        /// <summary>
        /// 研究报告 标题
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<VM_DMS_NewsList> GetYanJiuBaoGao(string code)
        {
            string sql = @"select top 1 Id=newsid_num,Step=0,PTime =f035d_12001,Title =f006v_12001
            from vm_dms_getnewsbycode
            where code in('7020201') and valid='1' and f025v_12001=@code
            order by f035d_12001 desc";
            SqlParameter[] values = { new SqlParameter("@code", code) };
            return SqlHelper.GetDataByColumnsLst<VM_DMS_NewsList>(DBConn.NewDMSConnstr, sql, values);
        }

        /// <summary>
        /// vm_dms_getnewsbycode 新闻内容
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public VM_DMS_GetNewsByCode GetNewsInfo(long id)
        {
            string sql = @"select top 1 Id=newsid_num,Title=f006v_12001,Content=f015v_12001,PTime=f035d_12001,KeyWord=f011v_12001,Stcok=f025v_12001,Source=f039v_12001,Summary=f011v_12001,Author='',Url='',biaoqian=f011v_12001
            from vm_dms_getnewsbycode
            where valid='1' and newsid_num=@id
            order by f035d_12001 desc";
            SqlParameter[] values = { new SqlParameter("@id", id) };
            return SqlHelper.GetDataByColumns<VM_DMS_GetNewsByCode>(DBConn.NewDMSConnstr, sql, values);
        }

        /// <summary>
        /// 券商晨会
        /// </summary>
        /// <returns></returns>
        public List<VM_DMS_GetNewsByCode> GetQianShangChengHuiList(int top)
        {
            string sql = @"SELECT top " + top + @" Id=newsid_num,Title=f006v_12001,Content=f015v_12001,PTime=f035d_12001
FROM vm_dms_getnewsbycode where code in ('7010103') order by F035D_12001 desc";
            return SqlHelper.GetDataByColumnsLst<VM_DMS_GetNewsByCode>(DBConn.NewDMSConnstr, sql, null);
        }

        /// <summary>
        /// 股市日历
        /// </summary>
        /// <returns></returns>
        public List<VM_DMS_GetNewsByCode> GetGuShiRiLiList()
        {
            DateTime sDate = DateTime.Now.AddMonths(-2);
            string sql = @"SELECT distinct Title=f006v_12001,PTime=f035d_12001 FROM vm_dms_getnewsbycode   
where code in ('5020203') and f035d_12001>@sDate";
            SqlParameter[] values = { new SqlParameter("@sDate", sDate) };
            return SqlHelper.GetDataByColumnsLst<VM_DMS_GetNewsByCode>(DBConn.NewDMSConnstr, sql, values);
        }
        /// <summary>
        /// 根据code获取新闻
        /// </summary>
        /// <param name="top">返回条数</param>
        /// <param name="code">新闻类型</param>
        /// <returns></returns>
        public List<NewsList> GetNewsByCode(int top, string code)
        {
            StringBuilder sb=new StringBuilder();
            sb.AppendLine(" select top " + top + " newsGuid=newsid_12001,Id=newsid_num,Step=f004v_12001, Title=f006v_12001,Summary=F013V_12001,Source=F011V_12001,PTime=f035d_12001,HyCode=f027v_12001,LongTitle=F007V_12001,codeName=F021V_12001,biaoqian=f011v_12001,bankuai=f027v_12001   from  vm_dms_getnewsbycode ");
            if (code == "6010102,3010202,3070204,6010101,6060201,3010201,7050102")
            {
                sb.AppendLine(" where code in ( " + code + "  ) and (f027v_12001 like '%1181%' or f027v_12001 like '%1072%' or f027v_12001 like '%1078%' or f027v_12001 like '%1185%' or f027v_12001 like '%1022%' or f027v_12001 like '%1211%')  and rectime>'2013-6-25'  order by  rectime desc  ");
            }
            else { 
            sb.AppendLine(" where code in ( " + code + "  ) order by  rectime desc  ");}
            //Tools tool = new Tools();
            //tool.AddSqlParameter("top", System.Data.SqlDbType.Int, top);
            //tool.AddSqlParameter("code", System.Data.SqlDbType.VarChar, code);
            return SqlHelper.GetDataByColumnsLst<NewsList>(DBConn.NewDMSConnstr, sb.ToString(), null);
        }

        /// <summary>
        /// 根据股票代码查询个股新闻
        /// </summary>
        /// <param name="top"></param>
        /// <param name="code"></param>
        /// <param name="stock"></param>
        /// <returns></returns>
        public List<NewsList> GetNewsByStock(int top, string code,string stock)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" select top " + top + " newsGuid=newsid_12001,Id=newsid_num, Title=f006v_12001,Summary=F013V_12001,Source=F011V_12001,PTime=f035d_12001 from vm_dms_getnewsbycode ");
            sb.AppendLine(" where code in ( " + code + "  ) and f025v_12001='" +stock+ "'  order by  rectime desc  ");
            //Tools tool = new Tools();
            //tool.AddSqlParameter("top", System.Data.SqlDbType.Int, top);
            //tool.AddSqlParameter("code", System.Data.SqlDbType.VarChar, code);
            return SqlHelper.GetDataByColumnsLst<NewsList>(DBConn.NewDMSConnstr, sb.ToString(), null);
        }
    }
}
